﻿using BaseAPI.model;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Cache;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using InventoryToPredictAPI.model;
using InventoryToPredictAPI.Util;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;

namespace InventoryToPredictAPI
{
    /// <summary>
    /// 回货计划
    /// author wuweihua
    /// Date   20171227
    /// </summary>
    [MustLogin]
    public class ReturPlanHandler : BaseHandler
    {
        #region 私有成员
        private VerticaDataHelper VerticaHelper { get; set; } = new VerticaDataHelper();
        #endregion

        #region 公开API

        #region 获取回货计划列表
        [MustLogin]
        [Description("回货计划列表")]
        [ReturnType(typeof(PaperData<ReturPlan.ReturPlanVOMOdel>))]
        public ApiResult GetReturPlanList([ParamDescription("供应商名称")] string ShortNames = "", [ParamDescription("大类")] string KindNames = "", [ParamDescription("小类")] string ItemNames = "", [ParamDescription("货号")] string MaterialIDs = "", [ParamDescription("页码")] int Size = 1, [ParamDescription("页数")] int Page = 100)
        {
            var count = ReturnCount(ShortNames, KindNames, ItemNames, MaterialIDs);
            var result = PaperData<ReturPlan.ReturPlanVOMOdel>.Create(count, Size, Page);
            IList<ReturPlan.ReturPlanVOMOdel> list = GetReturPlanListPage(ShortNames, KindNames, ItemNames, MaterialIDs, Size, Page);
            result.Items = list;
            return Success(result);
        }
        #endregion

        #region 获取ERP数据到库返回最新列表数据
        [MustLogin]
        [Description("获取Erp回货计划列表同步到Vertica")]
        [ReturnType(typeof(PaperData<ReturPlan.ReturPlanVOMOdel>))]
        public ApiResult SyncReturPlanByERP()
        {
            ReturPlanJob mReturPlanJob = new ReturPlanJob();
            int CanSyncReturPlanByERP = 0;
            if (RedisCache.Exists("CanSyncReturPlanByERP"))
            {
                CanSyncReturPlanByERP = RedisCache.GetInt("CanSyncReturPlanByERP", 0);
            }
            if (CanSyncReturPlanByERP == 1)
            {
                return Error("服务正在计算，请稍后再试!");
            }
            else
            {
                //加锁
                RedisCache.AddInt("CanSyncReturPlanByERP", 1, 60 * 24 * 30);
                mReturPlanJob.SyncVerticaByErp();
                //解锁
                RedisCache.AddInt("CanSyncReturPlanByERP", 0, 60 * 24 * 30);
            }


            return Success("获取成功");
        }
        #endregion

        #region Update 修改
        [MustLogin]
        [JsonType]
        [Description("修改")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("要插入的数据")] ReturPlan.ReturPlanVOUpdateMOdel voUpdateModel)
        {
            string companyId = UserInfo.UserCompanyId;
            string extrSource = UserInfo.UserExtrSource;
            if (string.IsNullOrEmpty(companyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(extrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }

            bool result = UpdateDBModelByVO(voUpdateModel);
            return Success(result ? "0" : "更新失败，请重试！");
        }
        #endregion

        #region 下框拉数据源
        /// <summary>
        /// 获取供应商
        /// </summary>
        /// <returns>typeof(IList<DownListModel.ShortInfo>)</returns>
        [MustLogin]
        [Description("获取供应商")]
        [ReturnType(typeof(IList<DownListModel.ShortInfo>))]
        public ApiResult GetShortNames()
        {
            IList<DownListModel.ShortInfo> result = null;
            string sql = $@"select DISTINCT VendCustID,ShortName from ndw.ndw_return_plan where ExtrSource='{UserInfo.UserExtrSource}'";
            var dt = VerticaHelper.GetDataTable(sql);
            result = ModelConvertHelper<DownListModel.ShortInfo>.ConvertToModel(dt);
            return Success(result);
        }


        /// <summary>
        /// 获取货号
        /// </summary>
        /// <param name="ShortNames"></param>
        /// <param name="KindNames"></param>
        /// <param name="ItemNames"></param>
        /// <returns>typeof(IList<DownListModel.MaterialInfo>)</returns>
        [MustLogin]
        [Description("获取货号")]
        [ReturnType(typeof(IList<DownListModel.MaterialInfo>))]
        public ApiResult GetMaterials([ParamDescription("供应商名称")] string ShortNames = "", [ParamDescription("大类")] string KindNames = "", [ParamDescription("小类")] string ItemNames = "")
        {
            IList<DownListModel.MaterialInfo> result = null;
            string sqlWhere = GetQueryWhere(ShortNames, KindNames, ItemNames);
            string sql = $@"select DISTINCT MaterialID from ndw.ndw_return_plan a where ExtrSource='{UserInfo.UserExtrSource}' {sqlWhere}";
            var dt = VerticaHelper.GetDataTable(sql);
            result = ModelConvertHelper<DownListModel.MaterialInfo>.ConvertToModel(dt);
            return Success(result);
            //[ParamDescription("供应商名称")] string ShortNames = "", [ParamDescription("大类")] string KindNames = "", [ParamDescription("小类")] string ItemNames = ""
        }
        #endregion
        #endregion

        #region 私有方法
        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="ShortNames">供应商</param>
        /// <param name="KindNames">大类</param>
        /// <param name="ItemNames">小类</param>
        /// <param name="MaterialIDs">货号</param>
        /// <returns></returns>
        private string GetQueryWhere(string ShortNames = "", string KindNames = "", string ItemNames = "", string MaterialIDs = "")
        {
            string sql = $" AND a.ChangeFlag <> 3";
            string temp = string.Empty;
            if (!string.IsNullOrEmpty(ShortNames))
            {
                temp = ShortNames.GetString();
                sql += $" AND a.VendCustID in {temp}";
            }
            if (!string.IsNullOrEmpty(KindNames))
            {
                temp = KindNames.GetString();
                sql += $" AND a.KindName in(select KindName from nods.nods_erp_SD_Mat_Kind where KindID in {temp})";
            }
            if (!string.IsNullOrEmpty(ItemNames))
            {
                temp = ItemNames.GetString();
                sql += $" AND a.MaterialID in(select MaterialID from vw_Mat_MaterialTOKind where ItemOrsub in {temp}) ";
            }
            if (!string.IsNullOrEmpty(MaterialIDs))
            {
                temp = MaterialIDs.GetString();
                sql += $" AND a.MaterialID in {temp} ";
            }
            return sql;
        }
        /// <summary>
        /// 获取数据总条数
        /// </summary>
        /// <param name="ShortNames">供应商</param>
        /// <param name="KindNames">大类</param>
        /// <param name="ItemNames">小类</param>
        /// <param name="MaterialIDs">货号</param>
        /// <returns></returns>
        private int ReturnCount(string ShortNames = "", string KindNames = "", string ItemNames = "", string MaterialIDs = "")
        {
            var countSql = string.Empty;
            #region 查询时获取行数
            string extrSource = UserInfo.UserExtrSource;
            string companyId = UserInfo.UserCompanyId;
            string sqlwhere = this.GetQueryWhere(ShortNames, KindNames, ItemNames, MaterialIDs);
            countSql = $@"select count(*) from ndw.ndw_return_plan a where ExtrSource = '{extrSource}' and CompanyID = '{companyId}' {sqlwhere}";
            #endregion 查询时获取行数;
            var count = Convert.ToInt32(VerticaHelper.GetQueryCount(countSql));
            return count;
        }
        /// <summary>
        /// 获取最新的回货计划列表
        /// </summary>
        /// <param name="ShortNames">供应商</param>
        /// <param name="KindNames">大类</param>
        /// <param name="ItemNames">小类</param>
        /// <param name="MaterialIDs">货号</param>
        /// <param name="Size">页码</param>
        /// <param name="Page">页数</param>
        /// <returns></returns>
        private IList<ReturPlan.ReturPlanVOMOdel> GetReturPlanListPage(string ShortNames, string KindNames, string ItemNames, string MaterialIDs, int Size, int Page)
        {
            string sql = string.Empty;
            string extrSource = UserInfo.UserExtrSource;
            string companyId = UserInfo.UserCompanyId;
            string sqlwhere = this.GetQueryWhere(ShortNames, KindNames, ItemNames, MaterialIDs);
            sql = $@"select ShortName,a.StockID,b.StockName,OrderBillNo,MaterialShortName,MaterialID,Qty,StartShipDate,RemainQty,AdjustStartShipDate,AdjustRemainQty,a.ChangeFlag
                     from ndw.ndw_return_plan a INNER join nods.nods_erp_Bas_Stock b on a.StockID = b.StockID 
                     where a.ExtrSource = '{extrSource}' and a.CompanyID = '{companyId}' {sqlwhere}
                     ORDER BY StartShipDate ASC,MaterialID
                     LIMIT {Size} offset {(Page - 1) * Size}";
            var dt = VerticaHelper.GetDataTable(sql);
            var list = ModelConvertHelper<ReturPlan.ReturPlanVOMOdel>.ConvertToModel(dt);
            return list;
        }
        /// <summary>
        /// 更新数据(回货计划)
        /// </summary>
        /// <param name="voUpdatemodel"></param>
        /// <returns></returns>
        private bool UpdateDBModelByVO(ReturPlan.ReturPlanVOUpdateMOdel voUpdatemodel)
        {
            bool result = false;
            string sql = $"UPDATE ndw.ndw_return_plan set AdjustStartShipDate='{voUpdatemodel.AdjustStartShipDate}' , AdjustRemainQty = {voUpdatemodel.AdjustRemainQty},UpdateNum = (UpdateNum+1),EtlDate=now()  where OrderBillNo = '{voUpdatemodel.OrderBillNo}' and MaterialID = '{voUpdatemodel.MaterialID}'";
            int count = VerticaHelper.GetNonQuery(sql);
            result = count > 0;
            return result;
        }
        #endregion
    }
}
